package com.ly.mpayment.persist.service.downrechargeflow;

import java.io.Serializable;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.math.RoundingMode;
import java.util.*;

import com.ly.mpayment.persist.entity.*;
import com.ly.mpayment.persist.service.upshoppro.IUpShopProService;
import com.ly.mpayment.rest.recharge.OrderThread;
import com.ly.mpayment.rest.recharge.RechargeRest;
import com.ly.mpayment.rest.system.BigDecimalUtil;
import com.ly.mpayment.rest.up.OrderHandler;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
import org.springframework.stereotype.Service;

import com.ly.mpayment.core.util.Config;
import com.ly.mpayment.core.util.DateUtils;
import com.ly.mpayment.core.util.JsonUtils;
import com.ly.mpayment.core.util.MapUtil;
import com.ly.mpayment.core.util.PaginationUtil;
import com.ly.mpayment.core.util.StringUtils;
import com.ly.mpayment.persist.hibernate.Page;
import com.ly.mpayment.persist.service.AbstractService;
import com.ly.mpayment.persist.service.debitlog.IDebitLogService;
import com.ly.mpayment.persist.service.downorderlog.IDownOrderLogService;
import com.ly.mpayment.persist.service.downorderrefund.IDownOrderRefundService;
import com.ly.mpayment.persist.service.downshopinfo.IDownShopInfoService;
import com.ly.mpayment.persist.service.uporder.IUporderService;
import com.ly.mpayment.persist.service.uporderlog.IUpOrderLogService;
import com.ly.mpayment.persist.service.uporderrefund.IUpOrderRefundService;
import com.ly.mpayment.persist.service.upshopinfo.IUpShopInfoService;
import com.ly.mpayment.rest.recharge.OrderProcessing;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

@Service
public class DownRechargeFlowServiceImpl extends AbstractService<DownRechargeFlow, Serializable> implements IDownRechargeFlowService{

	Logger logger = Logger.getLogger(DownRechargeFlowServiceImpl.class);

	@Autowired
	IDownShopInfoService shopService;


	@Autowired
	IUporderService orderService;

	@Autowired
	IDownOrderLogService logDownService;

	@Autowired
	IUpOrderLogService logUpService;

	@Autowired
	IDownOrderRefundService fundService;

	@Autowired
	IUpOrderRefundService refundSerevice;

	@Autowired
	IDebitLogService logService;

	@Autowired
	IUpShopInfoService upShopService;

	@Autowired
	IUporderService upOrderService;

	@Autowired
	ThreadPoolTaskExecutor taskExecutor;

	@Autowired
	private OrderProcessing orderProcessing;

	@Autowired
	private IUpShopProService upShopProService;

	@Autowired
	OrderThread orderThread;

	/**
	 * 查询刚达到的订单进行处理
	 * @param id
	 * @return
	 */
	@Override
	public DownRechargeFlow findById(String id) {
		return this.findUnique("select d from DownRechargeFlow d left join fetch d.downShopProduct left join fetch d.userFlow where d.downorderuuid = ?", id);
	}

	@Override
	public DownRechargeFlow findByOrderId(String downorderuuid) {
		return this.findUnique("select d from DownRechargeFlow d where d.downorderuuid = ?", downorderuuid);

	}

	@Override
	public DownRechargeFlow findByIdWating(String id) {
		return this.findUnique("select d from DownRechargeFlow d left join fetch d.downShopProduct left join fetch d.userFlow where d.status = 1 and d.downorderuuid = ?", id);
	}

	@Override
	public DownRechargeFlow findByUUid(String id) {
		return this.findUnique("select d from DownRechargeFlow d left join fetch d.userFlow left join fetch d.downShopProduct where d.status = 5 and d.downorderuuid = ?", id);
	}

//	@Override
//	public DownRechargeFlow findByUUidMaintenance(String id) {
//		return this.findUnique("select d from DownRechargeFlow d left join fetch d.userFlow left join fetch d.downShopProduct where d.status = 9 and d.downorderuuid = ?", id);
//	}

	/**
	 * 扣除余额
	 * @param flow
	 * @param shop
	 */
	@Override
	public void updatedeductionBalance(DownRechargeFlow flow, DownShopInfo shop){
		flow = findByUUid(flow.getDownorderuuid());
		//检查是否已经存在扣款记录 如果是则不在进行扣款
		DebitLog log = this.logService.findOrderLog(flow);
		if(log == null){
			if(this.shopService.updateDownShopInfoSubtract(shop,flow)){
				//更新订单充值次数
				//flow.setDownreqnum(flow.getDownreqnum() == 0 ? 1 : flow.getDownreqnum() + 1);
				flow.setStatus(Config.status[0]);
				this.updateEntity(flow);
			}else{
				logger.info("下游扣款失败:"+flow.getDiscountAmount()+",phone:"+flow.getDowonphonenum());
			}
		}else{
			//重提更新订单状态为可充值
			//flow.setDownreqnum(flow.getDownreqnum() == 0 ? 1 : flow.getDownreqnum() + 1);
			flow.setStatus(Config.status[0]);
			this.updateEntity(flow);
			logger.info("存在扣款订单:"+flow.getDiscountAmount()+",phone:"+flow.getDowonphonenum());
		}
	}

	/**
	 * 添加订单不扣除余额
	 * @param flow
	 * @param shop
	 * @return
	 */
	@Override
	public DownRechargeFlow addDownrechargeflow(DownRechargeFlow flow,DownShopInfo shop) {
		try {
			if(flow != null ){
				this.saveOrUpdate(flow);
				return flow;
			}
		} catch (Exception e) {
			e.printStackTrace();
			logger.info("addDownrechargeflow:"+e.getMessage());
		}
		return null;
	}

	@Override
	public  boolean updateDownrechargeflow(DownRechargeFlow flow,DownShopInfo shop) {
		if(flow != null && shop != null){
			if(fundService.findByDownOrderId(flow.getDownorderuuid()) == null){
				if(this.shopService.updateDownShopInfoAdd(shop,flow)){
					this.updateEntity(flow);
					return true;
				}
			}else{
				logger.info("存在退款订单:"+flow.getDiscountAmount()+","+flow.getDowonphonenum());
			}
		}
		return false;
	}
	/**
	 * 更新余额
	 */
	@Override
	public  boolean updateDownrechargeflowRefund(DownRechargeFlow flow,String errorMsg) {
		if(flow != null ){
			DownShopInfo shop = this.shopService.findShopInfoByUuid(flow.getUserFlow().getUseruuid());
			if(shop != null){
				if(fundService.findByDownOrderId(flow.getDownorderuuid()) == null){

					if(this.shopService.updateDownShopInfoAdd(shop,flow)){
						flow = this.findById(flow.getDownorderuuid());
						Date end = DateUtils.getNowDate();
						flow.setStatus(Config.status[2]);
						flow.setDownendtimesec(((Long)(end.getTime() - flow.getCreatetime().getTime())).intValue());
						flow.setDownendtime(end);
						flow.setRemark(errorMsg);
						//flow.setStatusremark(Config.orderFailA);

						this.updateEntity(flow);
						//充值失败退款记录
						DownOrderRefund fund = new DownOrderRefund();
						fund.setDownRechargeFlow(flow);
						fund.setDownrefundstatus(Config.status[0]+"");
						fund.setDownrefundamount(flow.getDiscountAmount());
						fund.setDownrefundtime(DateUtils.getNowDate());
						fund.setUser(flow.getUserFlow());
						fund.setBalance(shop.getDownshopbalance());
						fundService.addDownorderrefund(fund);
						logger.info("下游退款:"+flow.getDowonphonenum()+",flow:"+flow.getDownorderuuid());
						return true;
					};

				}else{
					logger.info("存在退款订单:"+flow.getDowonphonenum()+","+flow.getDownorderuuid());
					return true;
				}
			}else{
				logger.info("商家信息错误:"+flow.getDowonphonenum());
			}
		}
		return false;
	}

	@Override
	public Page<DownRechargeFlow> findPageDownrechargeflow(Map<String, Object> parameters, int start, int pageSize) {
		return this.findPageByNamed(getFlowOrderSql(parameters), parameters, start, pageSize);
	}
	private String getFlowOrderSql (Map<String, Object> parameters){
		String hql = "select f from DownRechargeFlow f left join fetch f.userFlow u left join fetch f.downShopProduct dos left join fetch dos.businessType bu  where and f.status != 0 ";

		if(parameters.containsKey(":operatoruid")){
			hql += " and f.operators ='".concat(parameters.get(":operatoruid").toString()).concat("' ");
			parameters.remove(":operatoruid");
		}
		if(parameters.containsKey(":useruuid")){
			hql += " and u.useruuid ='".concat(parameters.get(":useruuid").toString()).concat("' ");
			parameters.remove(":useruuid");
		}
		if(parameters.containsKey(":busuuid")){
			hql += " and bu.busuuid ='".concat(parameters.get(":busuuid").toString()).concat("' ");
			parameters.remove(":busuuid");
		}
		if(parameters.containsKey(":provinceuuid")){
			hql += " and f.province ='".concat(parameters.get(":provinceuuid").toString()).concat("' ");
			parameters.remove(":provinceuuid");
		}
		if(parameters.containsKey(":downreqid")){
			hql += " and f.downreqid = '".concat(parameters.get(":downreqid").toString()).concat("' ");
			parameters.remove(":downreqid");
		}
		if(parameters.containsKey(":phoneNum")){
			hql += " and f.dowonphonenum = '".concat(parameters.get(":phoneNum").toString().trim()).concat("' ");
			parameters.remove(":phoneNum");
		}
		if(parameters.containsKey(":downorderuuid")){
			hql += " and f.downorderuuid = '".concat(parameters.get(":downorderuuid").toString().trim()).concat("' ");
			parameters.remove(":downorderuuid");
		}
		if(parameters.containsKey(":status")){
			hql += " and f.status = '".concat(parameters.get(":status").toString().trim()).concat("' ");
			parameters.remove(":status");
		}
		if(parameters.containsKey(":callstatus")){
			hql += " and f.callStatus = ".concat(parameters.get(":callstatus").toString().trim()).concat(" ");
			parameters.remove(":callstatus");
		}
		if(parameters.containsKey(":downfacevalue")){
			hql += " and dos.downfacevalue = ".concat(parameters.get(":downfacevalue").toString().trim()).concat(" ");
			parameters.remove(":downfacevalue");
		}

		if(parameters.containsKey(":createtime")){
//			hql += " and DATE_FORMAT(f.createtime,'%Y-%m-%d') >= '".concat(parameters.get(":createtime").toString()).concat("'");
			hql += " and f.createtime >= '".concat(parameters.get(":createtime").toString()).concat("'");
			parameters.remove(":createtime");
		}
		if(parameters.containsKey(":endtime")){
//			hql += " and DATE_FORMAT(f.createtime,'%Y-%m-%d') <= '".concat(parameters.get(":endtime").toString()).concat("'");
			hql += " and f.createtime <= '".concat(parameters.get(":endtime").toString()).concat("'");
			parameters.remove(":endtime");
		}

		//3分钟未回调订单
		if(parameters.containsKey(":minute") && parameters.get(":minute").toString().equals("true")){
			hql += " and f.callStatus = 0 ";
			//当前时间减去三分钟
			hql += " and f.createtime <= '"+DateUtils.toLongDate(DateUtils.subtractionMinutes(3,DateUtils.getNowDate()))+"' ";
			parameters.remove(":minute");
		}
		hql += " order by f.createtime desc ";
		return hql;
	}

	@Override
	public Object findByShopId(String requestId,String uid) {
		return this.getSession().createSQLQuery("select d.downreqid from down_order d where d.downreqid = '"+requestId+"' and d.useruuid = '"+uid+"' "  ).uniqueResult();
	}

	@Override
	public  boolean updateDownrechargeflow(DownRechargeFlow flow) {
		if(flow != null){
			this.updateEntity(flow);
			return true;
		}
		return false;
	}

	/**
	 * 查询刚达到的订单
	 */
	@Override
	public List<DownRechargeFlow> findOrderFail(int pageSize ) {
//		 d.status = 3 or
		Calendar ca = Calendar.getInstance();
		ca.add(Calendar.MINUTE, -3);
		return this.findPageDataByNamed("select d from DownRechargeFlow d where d.status = 5 and d.createtime <= '"+DateUtils.toLongDate(ca.getTime())+"' order by d.createtime ", MapUtil.getMap(), 0, pageSize).getList();
	}

	/**
	 * 查询缓存订单
	 */
	@Override
	public List<DownRechargeFlow> findOrderMaintenance(int pageSize) {
		return this.findPageDataByNamed("select d from DownRechargeFlow d where d.status = 9 order by d.createtime ", MapUtil.getMap(), 0, pageSize).getList();
	}


	@Override
	public boolean updateCanelOrder(DownRechargeFlow flow,DownShopInfo shop, UpOrder upOrder,UpShopInfo shopUp,int status,User user) {
		if(flow != null && shop != null && upOrder != null && shopUp != null){
			if(Config.status[9] != status ){
				//不等于等待充值的则需要进行退款
				this.updateDownrechargeflow(flow, shop);
			}

			upOrder.setUpendtime(DateUtils.getNowDate());
			orderService.updateOrder(upOrder, shopUp);

			//添加日志
			DownOrderLog log = new DownOrderLog();
			log.setDownoperationtype(Config.orderMuError);
			log.setDownordertype(Config.orderType[1]);
			log.setDownRechargeFlow(flow);
			log.setRemark(Config.orderMuError);
			log.setUserLog(user);
			logDownService.addDownorderlog(log);

			UpOrderLog upLog = new UpOrderLog();
			upLog.setRemark(Config.orderMuError);
			upLog.setUpoperationtype(Config.orderMuError);
			upLog.setUpOrder(upOrder);
			upLog.setUpordertype(Config.orderType[1]);
			upLog.setUser(user);
			logUpService.addUporderlog(upLog);
			return true;
		}
		return false;
	}

	@Override
	public boolean updateCanelOrder(DownRechargeFlow flow, DownShopInfo shop,int status,User user) {
		if(flow != null && shop != null ){

			if(Config.status[0] == status || Config.status[1] == status || Config.status[5] == status || Config.status[11] == status){
				logger.info("flow:{}"+ status+","+Config.status[5]);
				//不等于缓存订单的则需要进行退款
				this.updateDownrechargeflow(flow, shop);
				//充值失败退款记录
				addDownOrderRefund(flow,shop);
			} else if(Config.status[9] == status){
				//缓存订单不进行退款
				//todo 需要检查是否有扣款记录 如果有则需要退款
				DebitLog log = this.logService.findOrderLog(flow);
				if(log == null){
					this.updateDownrechargeflow(flow);
				}else{
					//不等于缓存订单的则需要进行退款
					this.updateDownrechargeflow(flow, shop);
					addDownOrderRefund(flow,shop);
				}
			}
			addDownOrderLog(flow,user);
			return true;
		}
		return false;
	}

	/**
	 * 添加订单日志
	 */
	private void addDownOrderLog(DownRechargeFlow flow,User user){
		//添加日志
		DownOrderLog log = new DownOrderLog();
		log.setDownoperationtype(Config.orderMuError);
		log.setDownordertype(Config.orderType[1]);
		log.setDownRechargeFlow(flow);
		log.setRemark(Config.orderMuError);
		log.setUserLog(user);
		logDownService.addDownorderlog(log);
	}

	/**
	 * 添加退款记录
	 * @param flow
	 * @param shop
	 */
	private void addDownOrderRefund(DownRechargeFlow flow,DownShopInfo shop){
		DownOrderRefund fund = new DownOrderRefund();
		fund.setDownRechargeFlow(flow);
		fund.setDownrefundstatus(Config.status[0]+"");
		fund.setDownrefundamount(flow.getDiscountAmount());
		fund.setDownrefundtime(DateUtils.getNowDate());
		fund.setUser(flow.getUserFlow());
		fund.setBalance(shop.getDownshopbalance());
		fundService.addDownorderrefund(fund);
	}

	@Override
	public boolean updateCanelSuccessOrder(DownRechargeFlow flow, DownShopInfo shop,
			UpOrder upOrder, UpShopInfo shopUp) {
		if(flow != null && shop != null && upOrder != null && shopUp != null){
			if(fundService.findByDownOrderId(flow.getDownorderuuid()) == null){
				flow.setDownendtime(DateUtils.getNowDate());
				this.updateDownrechargeflow(flow, shop);

				//添加日志
				DownOrderLog log = new DownOrderLog();
				log.setDownoperationtype(Config.orderMuError);
				log.setDownordertype(Config.orderType[1]);
				log.setDownRechargeFlow(flow);
				log.setRemark(Config.orderMuError);
				logDownService.addDownorderlog(log);
			}


			if(refundSerevice.findByUpOrderId(upOrder.getUporderuuid()) == null){
				shopUp = this.upShopService.findById(shopUp.getUpshopuuid());
				if(shopUp != null){
					shopUp.setUpshopbalance(shopUp.getUpshopbalance().add(upOrder.getDiscountAmount()));
					upOrder.setUpendtime(DateUtils.getNowDate());
					orderService.updateOrder(upOrder, shopUp);

					//取消订单不会产生上游退款记录 如果订单成功则会产生退款记录
					UpOrderRefund refund = new UpOrderRefund();
					refund.setUpOrder(upOrder);
					refund.setUprefundamount(upOrder.getDiscountAmount());
					refund.setUprefundstatus(Config.status[0]+"");
					refund.setUprefundtime(DateUtils.getNowDate());
					refund.setUser(shopUp.getUser());
					refundSerevice.addUporderrefund(refund);
				}

				UpOrderLog upLog = new UpOrderLog();
				upLog.setRemark(Config.orderMuError);
				upLog.setUpoperationtype(Config.orderMuError);
				upLog.setUpOrder(upOrder);
				upLog.setUpordertype(Config.orderType[1]);
				logUpService.addUporderlog(upLog);
			}
			return true;
		}
		return false;
	}
//	useruuid","busuuid","provinceuuid","endtime","starttime","facevalue"
	@SuppressWarnings({ "rawtypes", "unchecked" })
	@Override
	public String getDownCount(Map<String, Object> parameters, int start, int pageSize) {
		Page pa = new Page();
		pa.setStart(start);
		pa.setPageSize(pageSize);
		Page page = PaginationUtil.queryPaginationSQL("select * from ("+getSql(parameters)+") temp ", pa, this.getSession());
		List lists = page.getResult();
		if(lists != null){
			JSONArray array = JsonUtils.getJSONARRAY();
//			[402880ec60e5bd8d0160e5d0f2210001, 111, 全国半年包, 中国电信, 165.0000000, null, null, 165.0000000, 15, 0, 0, 0]
			for(int i = 0 ; i < lists.size();i++){
				JSONObject obj = JsonUtils.getJSONOBJCT();
				Object [] object = (Object[]) lists.get(i);
				obj.put("downorderuuid", object[0] == null ? "" : object[0].toString());
				obj.put("username", object[1] == null ? "" : object[1].toString());
				obj.put("busname", object[2].toString());
				obj.put("operatorname", object[3].toString());
				obj.put("sumamount",  object[4] == null ? 0 : new BigDecimal(object[4].toString()).setScale(5));
				obj.put("success", object[5] == null ? 0 : new BigDecimal(object[5].toString()).setScale(5));
				obj.put("failt", object[6] == null ? 0 : new BigDecimal(object[6].toString()).setScale(5));
				obj.put("wait",  object[7] == null ? 0 : new BigDecimal(object[7].toString()).setScale(5));
				obj.put("sumorder",object[8] == null ? 0 :  object[8].toString());
				obj.put("successorder",object[9] == null ? 0 :  object[9].toString());
				obj.put("failtorder",object[10] == null ? 0 :  object[10].toString());
				obj.put("waitorder",object[11] == null ? 0 :  object[11].toString());
				obj.put("provincename",object[12] == null ? 0 :  object[12].toString());
				obj.put("profit",object[9] == null ? 0 :  (Integer.valueOf(object[9].toString()) / ( object[8] == null ? 0 :  Integer.valueOf(object[8].toString()))));
				array.add(obj);
			}
			page.setResult(array);
			return JsonUtils.convertObjectToJson(page);
		}
		return JsonUtils.getJSONARRAY().toString();
	}

	private String getSql(Map<String, Object> parameters){
		String sumSuccess = "";
		if(parameters.containsKey("useruuid")){
			sumSuccess += " and su.useruuid ='" +parameters.get("useruuid").toString().concat("' ");
		}
		if(parameters.containsKey("busuuid")){
			sumSuccess += " and bs.busuuid ='" +parameters.get("busuuid").toString().concat("' ");
		}
		if(parameters.containsKey("provinceuuid")){
			sumSuccess += " and su.provinceuuid  ='" +parameters.get("provinceuuid").toString().concat("' ");
		}
		if(parameters.containsKey("facevalue")){
			sumSuccess += " and su.rechargeamount =" +parameters.get("facevalue").toString().concat(" ");
		}
		if(parameters.containsKey("starttime")){
			sumSuccess += " and DATE_FORMAT(su.createtime,'%Y-%m-%d') >='" +parameters.get("starttime").toString().concat("' ");
		}
		if(parameters.containsKey("endtime")){
			sumSuccess += " and DATE_FORMAT(su.createtime,'%Y-%m-%d') <='" +parameters.get("endtime").toString().concat("' ");
		}

		String dfl = "";
		if(parameters.containsKey("useruuid")){
			dfl += " and dfl.useruuid ='" +parameters.get("useruuid").toString().concat("' ");
		}
		if(parameters.containsKey("busuuid")){
			dfl += " and bs.busuuid ='" +parameters.get("busuuid").toString().concat("' ");
		}
		if(parameters.containsKey("provinceuuid")){
			dfl += " and dfl.provinceuuid  ='" +parameters.get("provinceuuid").toString().concat("' ");
		}
		if(parameters.containsKey("facevalue")){
			dfl += " and dfl.rechargeamount =" +parameters.get("facevalue").toString().concat(" ");
		}
		if(parameters.containsKey("starttime")){
			dfl += " and DATE_FORMAT(dfl.createtime,'%Y-%m-%d') >='" +parameters.get("starttime").toString().concat("' ");
		}
		if(parameters.containsKey("endtime")){
			dfl += " and DATE_FORMAT(dfl.createtime,'%Y-%m-%d') <='" +parameters.get("endtime").toString().concat("' ");
		}

		String sql = "select df.useruuid,u.username,bs.busname, op.operatorname , sum(df.discount_amount) as 'sumamount',"
				+ " (select sum(su.discount_amount) from down_order su,businesstype bs where  su.useruuid = df.useruuid and df.buscode = su.buscode and bs.buscode = df.buscode "
				+ " and df.operatoruid = su.operatoruid  and su.status = 2 and  df.provinceuuid = su.provinceuuid "+sumSuccess+" ) "
				+ " as 'success' ,(select sum(su.discount_amount) from down_order su,businesstype bs  where su.useruuid = df.useruuid and df.buscode = su.buscode and "
				+ " bs.buscode = df.buscode and df.operatoruid = su.operatoruid and su.status = 3  and  df.provinceuuid = su.provinceuuid  and  df.provinceuuid = su.provinceuuid "+sumSuccess+" ) "
				+ " as 'failt',(select sum(su.discount_amount) from down_order su ,businesstype bs  where su.useruuid = df.useruuid and df.buscode = su.buscode and "
				+ " df.operatoruid = su.operatoruid  and su.status = 1  and  df.provinceuuid = su.provinceuuid )"
				+ " as 'wait',count(df.downorderuuid) as 'sumorder',(select count(dfl.downorderuuid) from down_order dfl,businesstype bs  where dfl.useruuid = df.useruuid and bs.buscode = dfl.buscode  and df.buscode = dfl.buscode and df.operatoruid = dfl.operatoruid "
				+ " and dfl.status = 2  and  df.provinceuuid = dfl.provinceuuid  "+dfl+") as 'successorder',(select count(dfl.downorderuuid) from down_order dfl ,businesstype bs  where dfl.useruuid = df.useruuid and bs.buscode = dfl.buscode  and df.buscode = dfl.buscode and df.operatoruid = dfl.operatoruid and "
				+ " dfl.status = 3 and  df.provinceuuid = dfl.provinceuuid  "+dfl+") as 'failtorder',(select count(dfl.downorderuuid) from down_order dfl,businesstype bs where dfl.useruuid = df.useruuid and bs.buscode = dfl.buscode and df.buscode = dfl.buscode and df.operatoruid = dfl.operatoruid "
						+ " and dfl.status = 1 and df.provinceuuid = dfl.provinceuuid "+dfl+" )"
				+ " as 'waitorder' ,pr.provincename from down_order df,user u,businesstype bs,provincialinformation pr ,operators op where u.useruuid = df.useruuid and"
				+ " bs.buscode = df.buscode and pr.provinceuuid = df.provinceuuid and op.operatoruid = df.operatoruid and df.status != 0  ";

		if(parameters.containsKey("useruuid")){
			sql += " and u.useruuid ='" +parameters.get("useruuid").toString().concat("' ");
		}
		if(parameters.containsKey("busuuid")){
			sql += " and bs.busuuid ='" +parameters.get("busuuid").toString().concat("' ");
		}
		if(parameters.containsKey("provinceuuid")){
			sql += " and pr.provinceuuid  ='" +parameters.get("provinceuuid").toString().concat("' ");
		}
		if(parameters.containsKey("facevalue")){
			sql += " and df.rechargeamount =" +parameters.get("facevalue").toString().concat(" ");
		}
		if(parameters.containsKey("starttime")){
			sql += " and DATE_FORMAT(df.createtime,'%Y-%m-%d') >='" +parameters.get("starttime").toString().concat("' ");
		}
		if(parameters.containsKey("endtime")){
			sql += " and DATE_FORMAT(df.createtime,'%Y-%m-%d') <='" +parameters.get("endtime").toString().concat("' ");
		}
		sql += " GROUP BY  df.useruuid,df.buscode,df.operatoruid ,bs.busname,pr.provincename,df.provinceuuid  ";
		return sql;
	}

	@Override
	public JSONArray exportDownCount(Map<String, Object> parameters) {
		List lists = this.getSession().createSQLQuery(getSql(parameters)).list();
		if(lists != null){
			JSONArray array = JsonUtils.getJSONARRAY();
//			[402880ec60e5bd8d0160e5d0f2210001, 111, 全国半年包, 中国电信, 165.0000000, null, null, 165.0000000, 15, 0, 0, 0]
			for(int i = 0 ; i < lists.size();i++){
				JSONObject obj = JsonUtils.getJSONOBJCT();
				Object [] object = (Object[]) lists.get(i);
				obj.put("useruuid", object[0] == null ? "" : object[0].toString());
				obj.put("username", object[1] == null ? "" : object[1].toString());
				obj.put("busname", object[2].toString());
				obj.put("operatorname", object[3].toString());
				obj.put("sumamount",  object[4] == null ? 0 : new BigDecimal(object[4].toString()).setScale(5));
				obj.put("success", object[5] == null ? 0 : new BigDecimal(object[5].toString()).setScale(5));
				obj.put("failt", object[6] == null ? 0 : new BigDecimal(object[6].toString()).setScale(5));
				obj.put("wait",  object[7] == null ? 0 :  new BigDecimal(object[7].toString()).setScale(5));
				obj.put("sumorder",object[8] == null ? 0 :  object[8].toString());
				obj.put("successorder",object[9] == null ? 0 :  object[9].toString());
				obj.put("failtorder",object[10] == null ? 0 :  object[10].toString());
				obj.put("waitorder",object[11] == null ? 0 :  object[11].toString());
				obj.put("provincename",object[12] == null ? 0 :  object[12].toString());
				obj.put("profit",object[9] == null ? 0 :  (Integer.valueOf(object[9].toString()) / ( object[8] == null ? 0 :  Integer.valueOf(object[8].toString()))));
				array.add(obj);
			}
			return array;
		}
		return JsonUtils.getJSONARRAY();
	}

	@Override
	public List exportdataFlowOrder(Map<String, Object> parameters) {
		return this.find(getFlowOrderSql(parameters));
	}

	@Override
	public DownRechargeFlow findShopUUIDorder(String requestId,String uid) {
		return this.findUnique("select d from DownRechargeFlow d left join fetch d.userFlow u left join fetch d.downShopProduct where d.downreqid = ? and u.useruuid =?", requestId,uid);
	}

	@Override
	public String findDownFlowCount(JSONObject json, int start, int pageSize) {
		Page pa = new Page();
		pa.setStart(start);
		pa.setPageSize(pageSize);
		Page page = PaginationUtil.queryPaginationSQL( "select * from ("+ getDownCountFlowSql(json)+") dfs", pa,
				this.getSession());
		List lists = page.getResult();
		if (lists != null && !lists.isEmpty()) {
			JSONArray array = JsonUtils.getJSONARRAY();
			for (int i = 0; i < lists.size(); i++) {
				JSONObject obj = JsonUtils.getJSONOBJCT();
				Object[] object = (Object[]) lists.get(i);
				obj.put("username", object[0] == null ? "" : object[0].toString());
				obj.put("proname", object[1] == null ? "" : object[1].toString());

//				obj.put("sumcount", object[2].toString());
//				obj.put("summoney",  object[3] == null ? 0 : new BigDecimal(object[3].toString()).setScale(5));
//				obj.put("sumdis", object[4] == null ? 0 : new BigDecimal(object[4].toString()).setScale(5));
				obj.put("sumcount", ( object[2] == null ? 0 : Integer.valueOf(object[2].toString()) + (object[5] == null ? 0 : Integer.valueOf(object[5].toString()))));
				obj.put("summoney", BigDecimalUtil.add((object[3] == null ? BigDecimal.ZERO : new BigDecimal(object[3].toString()).setScale(5)),object[6] == null ? BigDecimal.ZERO : new BigDecimal(object[6].toString()).setScale(5)));
				obj.put("sumdis", BigDecimalUtil.add(object[4] == null ? BigDecimal.ZERO : new BigDecimal(object[4].toString()).setScale(5),object[7] == null ? BigDecimal.ZERO : new BigDecimal(object[7].toString()).setScale(5)));

				obj.put("successnum", object[2] == null ? 0 :object[2].toString());
				obj.put("sucmoney", object[3] == null ? 0 : new BigDecimal(object[3].toString()).setScale(5));
				obj.put("sudis", object[4] == null ? 0 : new BigDecimal(object[4].toString()).setScale(5));

				obj.put("failtnum", object[5] == null ? 0 :object[5].toString());
				obj.put("famoney", object[6] == null ? 0 : new BigDecimal(object[6].toString()).setScale(5));
				obj.put("failtdis", object[7] == null ? 0 : new BigDecimal(object[7].toString()).setScale(5));
				obj.put("successRate",
						object[2] == null ? 0
								: new BigDecimal( (Double.valueOf(object[2].toString())
								/   (( object[2] == null ? 0 : Double.valueOf(object[2].toString()) + (object[5] == null ? 0 : Double.valueOf(object[5].toString())))) )).setScale(2, RoundingMode.HALF_UP) );

				array.add(obj);
			}
			page.setResult(array);
			return JsonUtils.convertObjectToJson(page);
		}
		return JsonUtils.getJSONARRAY().toString();
	}

	@Override
	public JSONArray exportDataDown(JSONObject json) {
		List lists = this.getSession().createSQLQuery(getDownCountFlowSql(json)).list();
		if (lists != null && !lists.isEmpty()) {
			JSONArray array = JsonUtils.getJSONARRAY();
			for (int i = 0; i < lists.size(); i++) {
				JSONObject obj = JsonUtils.getJSONOBJCT();
				Object[] object = (Object[]) lists.get(i);
				obj.put("username", object[0] == null ? "" : object[0].toString());
				obj.put("proname", object[1] == null ? "" : object[1].toString());

//				obj.put("sumcount", object[2].toString());
//				obj.put("summoney",  object[3] == null ? 0 : new BigDecimal(object[3].toString()).setScale(5));
//				obj.put("sumdis", object[4] == null ? 0 : new BigDecimal(object[4].toString()).setScale(5));

				obj.put("sumcount", ( object[2] == null ? 0 : Integer.valueOf(object[2].toString()) + (object[5] == null ? 0 : Integer.valueOf(object[5].toString()))));
				obj.put("summoney", BigDecimalUtil.add((object[3] == null ? BigDecimal.ZERO : new BigDecimal(object[3].toString()).setScale(5)),object[6] == null ? BigDecimal.ZERO : new BigDecimal(object[6].toString()).setScale(5)));
				obj.put("sumdis", BigDecimalUtil.add(object[4] == null ? BigDecimal.ZERO : new BigDecimal(object[4].toString()).setScale(5),object[7] == null ? BigDecimal.ZERO : new BigDecimal(object[7].toString()).setScale(5)));

				obj.put("successnum", object[2] == null ? 0 :object[2].toString());
				obj.put("sucmoney", object[3] == null ? 0 : new BigDecimal(object[3].toString()).setScale(5));
				obj.put("sudis", object[4] == null ? 0 : new BigDecimal(object[4].toString()).setScale(5));

				obj.put("failtnum", object[5] == null ? 0 :object[5].toString());
				obj.put("famoney", object[6] == null ? 0 : new BigDecimal(object[6].toString()).setScale(5));
				obj.put("failtdis", object[7] == null ? 0 : new BigDecimal(object[7].toString()).setScale(5));
				array.add(obj);
			}
			return array;
		}
		return JsonUtils.getJSONARRAY();
	}


	private String getDownCountFlowSql(JSONObject json){
		String sql = " select username,name ,sum(success),sum(price),sum(successmoney),sum(failt),sum(failtprice),sum(failtmoney)  from ( "
				+ " select (SELECT u.username from user u where u.useruuid = dw.useruuid ) as 'username'," +
				" (select pr.product_name from downshopproduct  pr where pr.downprouuid "
				+ " = dw.downprouuid) as 'name', "
				+ " case dw.`status`  when 2 then count(dw.downorderuuid) end as 'success', case dw.`status` when 2 then sum(dw.rechargeamount) end  as 'price',"
				+ " case dw.`status` when 2 then sum(dw.discount_amount) end as 'successmoney', case dw.`status` when 3 then count(dw.downorderuuid) end as 'failt', case dw.`status`"
				+ " when 3 then sum(dw.rechargeamount) end  as 'failtprice', case dw.`status` when 3 then sum(dw.discount_amount) end  as 'failtmoney'  from down_order dw "
				+ " where (dw.`status` = 2 or dw.`status` = 3)  ";

				//成功订单数
				if(json.containsKey("useruuid") && !StringUtils.isBlank(json.getString("useruuid"))){
					sql += "and dw.useruuid = '"+json.getString("useruuid")+"'";
				}
				if(json.containsKey("downprouuid") && !StringUtils.isBlank(json.getString("downprouuid"))){
					sql += "and dw.downprouuid = '"+json.getString("downprouuid")+"'";
				}
				if(json.containsKey("provinceuuid") && !StringUtils.isBlank(json.getString("provinceuuid"))){
					sql += "and dw.province = '"+json.getString("provinceuuid")+"'";
				}
				if(json.containsKey("starttime") && !StringUtils.isBlank(json.getString("starttime"))){
					sql += "and dw.createtime >= '"+json.getString("starttime")+"'";
				}
				if(json.containsKey("endtime") && !StringUtils.isBlank(json.getString("endtime"))){
					sql += "and dw.createtime <= '"+json.getString("endtime")+"'";
				}
				if(json.containsKey("operatoruid") && !StringUtils.isBlank(json.getString("operatoruid"))){
					sql += "and dw.operatoruid = '"+json.getString("operatoruid")+"'";
				}
				if(json.containsKey("provinceuuid") && !StringUtils.isBlank(json.getString("provinceuuid"))){
					sql += "and dw.province = '"+json.getString("provinceuuid")+"'";
				}
		 sql += " GROUP BY dw.downprouuid,dw.`status` ) tts GROUP BY username,name ";
		return sql;
	}



	@Override
	public boolean updateDownrechargeflow(DownRechargeFlow flow,int callStatus,int callNum) {
		if(flow != null){
			if(this.getSession().createSQLQuery("update down_order set call_status = "+callStatus+",call_num = "+callNum+" where downorderuuid = '"+flow.getDownorderuuid()+"'").executeUpdate() > 0){
				return true;
			}
		}
		return false;
	}

	@Override
	public boolean updateDownrechargeflow(String downorderuuid, int callNum) {
		if(downorderuuid != null){
			if(this.getSession().createSQLQuery("update down_order set call_num = "+callNum+" where downorderuuid = '"+downorderuuid+"'").executeUpdate() > 0){
				return true;
			}
		}
		return false;
	}

	@Override
	public List<DownRechargeFlow> findOrderNotCallFail(int pageSize) {
		return this.findPageDataByNamed("select d from DownRechargeFlow d where (d.status = 3 or d.status = 4) and  d.callStatus = 0 and d.callNum < 3 order by d.createtime ", MapUtil.getMap(), 0, pageSize).getList();
	}

	@Autowired
	OrderHandler orderHandler;

	/**
	 *
	 * @param orderUp
	 * @param errorMsg
	 */
	@Override
	public void updateOrderFailt(UpOrder orderUp, String errorMsg) {
		//人工操作失败。不检查
		UpOrder order = orderService.findByCallId(orderUp.getUporderuuid());
		//检查渠道人工和产品开关 如果其中一个开启都转人工 否则按失败处理
		UpShopInfo upShopInfo = upShopService.findByUserId(order.getUserOrder().getUseruuid());
		if(order != null && order.getUporderuuid() != null){
			//order = upOrderService.findByCallId(order.getUporderuuid());
			//如果上游订单是成功状态则需要进行退款修改状态为失败
			if(order.getStatus() == Config.status[1]){
				if(refundSerevice.findByUpOrderId(order.getUporderuuid()) == null){
					upShopInfo.setUpshopbalance(upShopInfo.getUpshopbalance().add(order.getDiscountAmount()));
					upShopService.updateUpshopinfo(upShopInfo);
					//添加退款日志
					logger.info("退款上游订单:" + order.getDiscountAmount() + "," + order.getUpphonenum());
					UpOrderRefund log = new UpOrderRefund();
					log.setUpOrder(order);
					log.setUprefundamount(order.getDiscountAmount());
					log.setUprefundstatus(order.getUpphonenum());
					log.setUprefundtime(DateUtils.getNowDate());
					log.setUser(upShopInfo.getUser());
					log.setBalance(upShopInfo.getUpshopbalance());
					refundSerevice.addUporderrefund(log);
				}else{
					logger.info("退款上游订单已存在:" + order.getDiscountAmount() + "," + order.getUpphonenum());
				}
			}
			//订单失败时 只处理充值中 压单 人工
			if(order.getStatus() == Config.status[0] || order.getStatus() == Config.status[10] || order.getStatus() == Config.status[11]
					||  order.getStatus() == Config.status[1] ||  order.getStatus() == Config.status[5]){
				DownRechargeFlow flow = findById(order.getDownRechargeFlow().getDownorderuuid()) ;

				Date end = DateUtils.getNowDate();
				//回调时需要根据结果判断成功或者失败 ，当前默认
				order.setUpendtime(end);
				order.setUpendtimesec(((Long)(end.getTime() - order.getCreatetime().getTime())).intValue());
				order.setStatus(Config.status[2]);
				order.setCallBack(Config.callStatus[1]);
				order.setSendStatus(Config.sendStatus[0]);
				order.setRemark(errorMsg);
				upOrderService.updateOrder(order);

				if(updateDownrechargeflowRefund(flow,errorMsg)){
					try{
						orderProcessing.sendResultDownShop(flow,true);
					}catch (Exception e){
						e.printStackTrace();
						logger.info("OrderProcessing:"+order.getUpphonenum());
						OrderProcessing.callResultQueue.add(flow);
					}
					//回调成功删除
					logger.info("orderHanlderFailt_success:"+order.getUpphonenum());
				}
			}else{
				logger.info("异常订单:"+order.getUpphonenum());
				//throw new RuntimeException("订单状态异常:"+order.getUpphonenum());
			}
		}else{
			logger.info("异常订单:"+order);
		}
	}
	@Override
	public Integer findByPhoneCount(String phone) {
		String sql = " select count(*) from down_order d where d.dowonphonenum = '"+phone+"' and d.status = 3 " +
				" and d.createtime >= '"+DateUtils.getNowDateYMD()+" 00:00:00' and d.createtime <= '"+DateUtils.getNowDateYMD()+" 23:59:59' ";
		return ((BigInteger) this.getSession().createSQLQuery(sql).uniqueResult()).intValue();
	}

	@Override
	public Page<DownRechargeFlow> findByPagePhone(Map<String, Object> parameters, int start, int pageSize) {
		return this.findPageByNamed(" select  d from DownRechargeFlow d where d.status = 2 order by d.createtime", parameters, start, pageSize);
	}

	@Override
	public void addOrderMsg(DownRechargeFlow flow) {
		this.saveOrUpdate(flow);
	}

	@Override
	public int findbyProduct(String downprouuid) {
		return BigDecimal.valueOf(this.findLong("SELECT count(*) from DownRechargeFlow d  where d.downShopProduct.downprouuid = ?",downprouuid)).intValue();
	}

	@Override
	public String findDownOrderCount(JSONObject json, int start, int pageSize) {
		Page pa = new Page();
		pa.setStart(start);
		pa.setPageSize(pageSize);
		Page page = PaginationUtil.queryPaginationSQL( "select * from ("+ getfindDownOrderCountSql(json)+") dfs", pa,
				this.getSession());
		List lists = page.getResult();
		if (lists != null && !lists.isEmpty()) {
			JSONArray array = JsonUtils.getJSONARRAY();
			for (int i = 0; i < lists.size(); i++) {
				JSONObject obj = JsonUtils.getJSONOBJCT();
				Object[] object = (Object[]) lists.get(i);
				obj.put("sum", object[0] == null ? "" : object[0].toString());
				obj.put("sumprice", object[1] == null ? "" : object[1].toString());
				obj.put("summoney", ( object[2] == null ? "" : object[2].toString()));
				obj.put("operatorname",  object[3] == null ? "" : object[3].toString() );
				obj.put("username", object[4] == null ? "" : object[4].toString());
				obj.put("sumname", object[5] == null ? 0 :object[5].toString());

				array.add(obj);
			}
			page.setResult(array);
			return JsonUtils.convertObjectToJson(page);
		}
		return JsonUtils.getJSONARRAY().toString();
	}

	private String getfindDownOrderCountSql(JSONObject json){
		String temp = "";
		if(!StringUtils.isBlank(json.getString("useruuid"))){
			String str [] = json.getString("useruuid").split(",");
			for(int i = 0 ; i < str.length; i++){
				temp += "'"+str[i]+"',";
			}
			temp = temp.substring(0,temp.length() - 2);
		}
		String sql = " SELECT count(*) 'zs',sum(d.rechargeamount)'zmz',sum(d.discount_amount)'mj',d.operatorname,'','合计'" +
			" from down_order d " +
			" left join user u on u.useruuid = d.useruuid " +
			" where 1 = 1 ";
			if(json.containsKey("operatorname") && !StringUtils.isBlank(json.getString("operatorname"))){
				sql += " and d.operatorname = '"+json.getString("operatorname")+"'";
			}
			if(json.containsKey("provincename") && !StringUtils.isBlank(json.getString("provincename"))){
				sql += " and d.province = '"+json.getString("provincename")+"'";
			}
			if(json.containsKey("starttime") && !StringUtils.isBlank(json.getString("starttime"))){
				sql += " and d.createtime >= '"+json.getString("starttime")+"'";
			}
			if(json.containsKey("endtime") && !StringUtils.isBlank(json.getString("endtime"))){
				sql += " and d.createtime <= '"+json.getString("endtime")+"'";
			}
			sql +=
			" GROUP BY d.operatorname UNION" +
			" SELECT count(*) 'zs',sum(d.rechargeamount)'zmz',sum(d.discount_amount)'mj',d.operatorname,u.username,'成功'" +
			" from down_order d " +
			" left join user u on u.useruuid = d.useruuid " +
			" where d.`status` = 2 " ;
			if(json.containsKey("operatorname") && !StringUtils.isBlank(json.getString("operatorname"))){
				sql += " and d.operatorname = '"+json.getString("operatorname")+"'";
			}
			if(json.containsKey("provincename") && !StringUtils.isBlank(json.getString("provincename"))){
				sql += " and d.province = '"+json.getString("provincename")+"'";
			}
			if(json.containsKey("starttime") && !StringUtils.isBlank(json.getString("starttime"))){
				sql += " and d.createtime >= '"+json.getString("starttime")+"'";
			}
			if(json.containsKey("endtime") && !StringUtils.isBlank(json.getString("endtime"))){
				sql += " and d.createtime <= '"+json.getString("endtime")+"'";
			}
			if(!StringUtils.isBlank(temp)){
				sql += " and d.useruuid in ( "+temp+"')";
			}

			sql +=
			" GROUP BY d.operatorname UNION" +
			" SELECT count(*) 'zs',sum(d.rechargeamount)'zmz',sum(d.discount_amount)'mj',d.operatorname,u.username,'失败'" +
			" from down_order d " +
			" left join user u on u.useruuid = d.useruuid " +
			" where d.`status` = 3";
			if(json.containsKey("operatorname") && !StringUtils.isBlank(json.getString("operatorname"))){
				sql += " and d.operatorname = '"+json.getString("operatorname")+"'";
			}
			if(json.containsKey("provincename") && !StringUtils.isBlank(json.getString("provincename"))){
				sql += " and d.province = '"+json.getString("provincename")+"'";
			}
			if(json.containsKey("starttime") && !StringUtils.isBlank(json.getString("starttime"))){
				sql += " and d.createtime >= '"+json.getString("starttime")+"'";
			}
			if(json.containsKey("endtime") && !StringUtils.isBlank(json.getString("endtime"))){
				sql += " and d.createtime <= '"+json.getString("endtime")+"'";
			}
			if(!StringUtils.isBlank(temp)){
				sql += " and d.useruuid in ( "+temp+"')";
			}
			sql += " GROUP BY d.operatorname" ;
		return sql;
	}
}
